package com.hisense.proxy.service.ncc.iufo;

import com.alibaba.excel.EasyExcel;
import com.hisense.proxy.busitrans.vo.zndz.ZndzFtpPwdRelationExcelVO;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URI;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * <p>
 *
 * </p>
 *
 * @author tangxqa
 * @since 2022/10/11 20:15
 */
@Service
@Slf4j
public class IufoSqlGenerateService {

    private static final String reportPkTablePrefix = "IUFO_MERGE_MEASPUB_";


    @SneakyThrows
    public String getSql(String reportPk, List<MeasureMngExcelData> allData, List<MeasureMngExcelData> specificData, String sqlTemplate) {

        if (allData == null || allData.size() == 0) {
            throw new Exception("sheet_all 中没有数据！");
        }

        if (specificData == null || specificData.size() == 0) {
            throw new Exception("sheet_specific中没有数据！");
        }


        Map<Integer, List<MeasureMngExcelData>> specificGroupByRow
                = specificData.stream()
                .filter(item -> Strings.isNotBlank(item.getTableName()))
                .collect(Collectors.groupingBy(MeasureMngExcelData::getRow));

        Map<Integer, List<MeasureMngExcelData>> allGroupByRow
                = allData.stream()
                .filter(item -> Strings.isNotBlank(item.getTableName()))
                .collect(Collectors.groupingBy(MeasureMngExcelData::getRow));

        // 所有列，用于补齐
        Set<String> allColumnSet = allData.stream().map(MeasureMngExcelData::getColumn).collect(Collectors.toSet());

        Iterator<Map.Entry<Integer, List<MeasureMngExcelData>>> iterator = allGroupByRow.entrySet().iterator();
        StringBuilder result = new StringBuilder("生成结果：\n");
        while (iterator.hasNext()) {
            Map.Entry<Integer, List<MeasureMngExcelData>> next = iterator.next();
            Integer row = next.getKey();
            List<MeasureMngExcelData> allMngExcelDatas = next.getValue();
            HashSet<MeasureMngExcelData> columnSet = new HashSet<>(allMngExcelDatas);
            List<MeasureMngExcelData> columns = new ArrayList<>(columnSet);
            columns = columns.stream().sorted((a, b) -> {

                String aColumn = a.getColumn();
                String bColumn = b.getColumn();
                if (aColumn.length() == bColumn.length()) {
                    return aColumn.compareTo(bColumn);
                }

                return aColumn.length() - bColumn.length();
            }).collect(Collectors.toList());


            List<MeasureMngExcelData> measureMngExcelDatas = specificGroupByRow.get(row);
            if (measureMngExcelDatas == null || measureMngExcelDatas.size() == 0) {
                log.error("====================第" + row + "行数据没有本月余额字段，跳过！！！\n");
                continue;
            }

            if (row > 0) {
                result.append("\n union all \n");
            }

            MeasureMngExcelData specificExcelData = measureMngExcelDatas.get(0);
            // 字段
            String tableColumn = specificExcelData.getTableColumn();
            // 表名
            String tableName = specificExcelData.getTableName();
            // 新表名
            String newTableName = tableName + "_" + tableColumn;
            // 带主键生成表名
            String pkSuffix = reportPk.substring(reportPk.length() - 4);
            String pkTableName = reportPkTablePrefix + pkSuffix;
            // 列名枚举
            StringBuilder columnsBuilder = new StringBuilder();
            for (MeasureMngExcelData column : columns) {
                if (column.getRow() != row) {
                    continue;
                }

                columnsBuilder.append("to_char ( a1.")
                        .append(column.getTableColumn())
                        .append(" ) ")
                        .append(column.getColumn())
                        .append(",");
            }

            String[] templates = sqlTemplate.split("====");
            String mainTemplate = templates[0];
            String leftJoinTemplate = templates[1];

            String mainLineSql = mainTemplate.replace("{行号}", String.valueOf(row))
                    .replace("{字段}", tableColumn)
                    .replace("{表名}", tableName)
                    .replace("{新表名}", newTableName)
                    .replace("{带主键生成表名}", pkTableName);

            String allSql = getLeftJoinSql(mainLineSql, columnSet, allColumnSet, pkTableName, row, leftJoinTemplate);
            result.append(allSql);

        }
        return result.toString();
    }


    private String getLeftJoinSql(String partOneSql, HashSet<MeasureMngExcelData> columnSet, Set<String> allColumnSet, String pkTableName,
                                  int row, String sqlTemplate) {
        int tableNo = 6;

        Map<String, List<MeasureMngExcelData>> collect =
                columnSet.stream().collect(Collectors.groupingBy(MeasureMngExcelData::getTableName));
        Iterator<Map.Entry<String, List<MeasureMngExcelData>>> iterator = collect.entrySet().iterator();
        StringBuilder leftJoinSql = new StringBuilder();

        List<TableAliasColumn> aliasColumns = new ArrayList<>();

        while (iterator.hasNext()) {
            Map.Entry<String, List<MeasureMngExcelData>> next = iterator.next();
            String tableName = next.getKey();
            List<MeasureMngExcelData> datas = next.getValue();
            StringBuilder columnsBuilder = new StringBuilder();

            for (MeasureMngExcelData column : datas) {
                aliasColumns.add(new TableAliasColumn(tableNo, column.getColumn()));

                if (column.getRow() != row) {
                    continue;
                }

                columnsBuilder.append("to_char ( a1.")
                        .append(column.getTableColumn())
                        .append(" ) ")
                        .append(column.getColumn())
                        .append(",");
            }
            String lineSql = sqlTemplate
                    .replace("{表名}", tableName)
                    .replace("{带主键生成表名}", pkTableName)
                    .replace("{列名枚举}", columnsBuilder.toString())
                    .replace("{左连接别名}", "t" + tableNo);
            tableNo++;

            leftJoinSql.append(lineSql + "\n");
        }

        appendNotExistColumn(aliasColumns, allColumnSet);
        List<TableAliasColumn> aliasCollector =
                aliasColumns.stream().sorted((a, b) -> {
                            String aColumn = a.getColumn();
                            String bColumn = b.getColumn();
                            if (aColumn.length() == bColumn.length()) {
                                return aColumn.compareTo(bColumn);
                            }

                            return aColumn.length() - bColumn.length();
                        })
                        .collect(Collectors.toList());
        String aliasStr = aliasCollector.stream().map(item -> {
            StringBuilder aliasBuilder = new StringBuilder();
            if (item.isAppend()) {
                aliasBuilder.append("'' ")
                        .append(item.getColumn());
            } else {
                aliasBuilder.append("t")
                        .append(item.tableNo)
                        .append(".")
                        .append(item.getColumn());
            }

            return aliasBuilder.toString();
        }).collect(Collectors.joining(","));
        partOneSql = partOneSql.replace("{排序后列名}", aliasStr);
        partOneSql = partOneSql + "\n" + leftJoinSql.toString().trim();

        return partOneSql;
    }

    private void appendNotExistColumn(List<TableAliasColumn> aliasColumns, Set<String> allColumnSet) {
        int tableNo = 0;
        for (String column : allColumnSet) {
            boolean exist = false;
            for (TableAliasColumn tableAliasColumn : aliasColumns) {
                tableNo = tableAliasColumn.getTableNo();
                if (tableAliasColumn.getColumn().equals(column)) {
                    exist = true;
                }
            }

            if (exist) {
                continue;
            }

            aliasColumns.add(new TableAliasColumn(tableNo, column, true));
        }

    }

    @SneakyThrows
    private byte[] getInputstreamBytes(InputStream inputStream) {
        byte[] bytes;
        try {
            bytes = new byte[inputStream.available()];
            inputStream.read(bytes);
        } finally {
            inputStream.close();
        }
        return bytes;
    }


    @SneakyThrows
    public static void main(String[] args) {

        List<MeasureMngExcelData> allData = EasyExcel.read(new File("E:\\Users\\tangxqa\\Desktop\\指标管理-test.xlsx"))
                .head(MeasureMngExcelData.class)
                .headRowNumber(1)
                .sheet("all")
                .doReadSync();
        List<MeasureMngExcelData> specificData = EasyExcel.read(new File("E:\\Users\\tangxqa\\Desktop\\指标管理-test.xlsx"))
                .head(MeasureMngExcelData.class)
                .headRowNumber(1)
                .sheet("specific")
                .doReadSync();

        String sqlTemplate = new String(Files.readAllBytes(Paths.get(URI.create(""))), StandardCharsets.UTF_8);

        String sql = new IufoSqlGenerateService().getSql("10011A100000002LH5Y2", allData, specificData, sqlTemplate);
        System.out.println(sql);
    }


    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    private class TableAliasColumn {
        private int tableNo;

        private String column;

        private boolean isAppend = false;

        public TableAliasColumn(int tableNo, String column) {
            this.tableNo = tableNo;
            this.column = column;
        }

    }

}
